﻿
using CodeGenerator.Common;
using MySql.Data.MySqlClient;
using System;
using System.Data;
using System.Runtime.InteropServices;
using System.Text;
namespace CodeGenerator
{
    public class DbMySQLObject : IDbObject
    {
        private string _dbconnectStr;
        private MySqlConnection connect;
        private bool isdbosp;

        public DbMySQLObject()
        {
            this.isdbosp = false;
            this.connect = new MySqlConnection();
            this.IsDboSp();
        }

        public DbMySQLObject(string DbConnectStr)
        {
            this.isdbosp = false;
            this.connect = new MySqlConnection();
            this._dbconnectStr = DbConnectStr;
            this.connect.ConnectionString = DbConnectStr;
        }

        public DbMySQLObject(bool SSPI, string Ip, string User, string Pass) : this(SSPI, Ip, User, Pass, "3306")
        {
        }

        public DbMySQLObject(bool SSPI, string Ip, string User, string Pass, string port)
        {
            this.isdbosp = false;
            this.connect = new MySqlConnection();
            this.connect = new MySqlConnection();
            if (SSPI)
            {
                this._dbconnectStr = $"server={Ip};user id={User}; password={Pass}; Port={port};database=; pooling=false";
            }
            else
            {
                this._dbconnectStr = $"server={Ip};user id={User}; password={Pass}; Port={port};database=; pooling=false";
            }
            this.connect.ConnectionString = this._dbconnectStr;
        }

        private MySqlCommand BuildQueryCommand(MySqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            MySqlCommand command = new MySqlCommand(storedProcName, connection)
            {
                CommandType = CommandType.StoredProcedure
            };
            foreach (MySqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    if (((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Input)) && (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }
            return command;
        }

        private int CompareStrByOrder(string x, string y)
        {
            if (x == "")
            {
                if (y == "")
                {
                    return 0;
                }
                return -1;
            }
            if (y == "")
            {
                return 1;
            }
            return x.CompareTo(y);
        }

      

        public bool DeleteTable(string DbName, string TableName)
        {
            try
            {
                MySqlCommand command = this.OpenDB(DbName);
                command.CommandText = ("DROP TABLE " + TableName) ?? "";
                command.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public MySqlDataReader ExecuteReader(string DbName, string strSQL)
        {
            MySqlDataReader reader2;
            try
            {
                this.OpenDB(DbName);
                reader2 = new MySqlCommand(strSQL, this.connect).ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (MySqlException exception)
            {
                throw exception;
            }
            return reader2;
        }

        public int ExecuteSql(string DbName, string SQLString)
        {
            MySqlCommand command = this.OpenDB(DbName);
            command.CommandText = SQLString;
            return command.ExecuteNonQuery();
        }

        public DataTable GetColumnInfoList(string DbName, string TableName)
        {
            DataTable table2;
            try
            {
                string strSQL = $"SHOW FULL COLUMNS FROM `{TableName}`";
                DataTable table =Utils.CreateColumnTable();
                MySqlDataReader reader = this.ExecuteReader(DbName, strSQL);
                for (int i = 1; reader.Read(); i++)
                {
                    string str2=string.Empty ;
                    string str3 = string.Empty;
                    DataRow row = table.NewRow();
                    row[0] = i.ToString();
                    if (!object.Equals(reader["Field"], null) && !object.Equals(reader["Field"], DBNull.Value))
                    {
                        row["ColumnName"] = reader["Field"].ToString();
                        string preci = "";
                        string scale = "";
                        str2 = reader["Type"].ToString();
                        this.TypeNameProcess(str2, out str2, out str3, out preci, out scale);
                        row["TypeName"] = str2;
                        row["Length"] = str3;
                        row["Precision"] = preci;
                        row["Scale"] = scale;
                        if (object.Equals(reader["Key"], null) || object.Equals(reader["Key"], DBNull.Value))
                        {
                            row["IsPrimaryKey"] = (reader["Key"].ToString().Trim() == "PRI") ? "√" : "";
                        }
                        row["IsColumnNull"] = (reader["Null"].ToString().Trim() == "YES") ? "√" : "";
                        row["DefaultValue"] = reader["Default"].ToString();
                        row["IsIdentity"] = reader["Extra"].ToString().Trim() == "auto_increment" ? "√" : "";
                        row["Description"] = reader["Comment"].ToString();
                    } 
                    table.Rows.Add(row);
                }
                reader.Close();
                table2 = table;
            }
            catch (Exception exception)
            {
                throw new Exception("获取列数据失败" + exception.Message);
            }
            return table2;
        }

        public DataTable GetColumnInfoListSP(string DbName, string TableName) =>
            null;

        public DataTable GetColumnList(string DbName, string TableName) =>
            this.GetColumnInfoList(DbName, TableName);

        public DataTable GetColumnListSP(string DbName, string TableName) =>
            this.GetColumnInfoList(DbName, TableName);

        public DataTable GetDBList()
        {
            DataTable table = new DataTable();
            DataColumn column = new DataColumn("name", typeof(string));
            table.Columns.Add(column);
            string strSQL = "SHOW DATABASES";
            using (MySqlDataReader reader = this.ExecuteReader("mysql", strSQL))
            {
                while (reader.Read())
                {
                    DataRow row = table.NewRow();
                    row[0] = reader.GetString(0);
                    table.Rows.Add(row);
                }
                reader.Close();
            }
            return table;
        }

        public DataTable GetKeyName(string DbName, string TableName)
        {
            var columnInfoList = this.GetColumnInfoList(DbName, TableName);
            return Utils.GetKeyName(columnInfoList, DatabaseType.MySql);
        }

        public string GetObjectInfo(string DbName, string objName)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select b.text ");
            builder.Append("from sysobjects a, syscomments b  ");
            builder.Append("where a.xtype='p' and a.id = b.id  ");
            builder.Append(" and a.name= '" + objName + "'");
            return this.GetSingle(DbName, builder.ToString()).ToString();
        }

        public DataTable GetProcInfo(string DbName) =>
            null;

        public DataTable GetProcs(string DbName) =>
            null;

        public object GetSingle(string DbName, string SQLString)
        {
            try
            {
                MySqlCommand command = this.OpenDB(DbName);
                command.CommandText = SQLString;
                object objA = command.ExecuteScalar();
                if (object.Equals(objA, null) || object.Equals(objA, DBNull.Value))
                {
                    return null;
                }
                return objA;
            }
            catch
            {
                return null;
            }
        }

        public DataTable GetTabData(string DbName, string TableName)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(("select * from " + TableName) ?? "");
            return this.Query(DbName, builder.ToString()).Tables[0];
        }

        public DataTable GetTabData(string DbName, string TableName, int TopNum)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select  * from [" + TableName + "]");
            if (TopNum > 0)
            {
                builder.Append(" limit 0," + TopNum.ToString());
            }
            return this.Query(DbName, builder.ToString()).Tables[0];
        }

        public DataTable GetTabDataBySQL(string DbName, string strSQL) =>
            this.Query(DbName, strSQL).Tables[0];

        public DataTable GetTables(string DbName)
        {
            string sQLString = "SHOW TABLE STATUS";
            DataTable table = this.Query(DbName, sQLString).Tables[0];
            table.Columns[0].ColumnName = "name";
            DataRow[] rowArray = table.Select("Comment<>'VIEW' and Engine is not null");
            DataTable table2 = table.Clone();
            foreach (DataRow row in rowArray)
            {
                DataRow row2 = table2.NewRow();
                row2["name"] = row["name"];
                table2.Rows.Add(row2);
            }
            return table2;
        }

        public string GetTableScript(string DbName, string TableName)
        {
            string str = "";
            string strSQL = "SHOW CREATE TABLE " + TableName;
            MySqlDataReader reader = this.ExecuteReader(DbName, strSQL);
            while (reader.Read())
            {
                str = reader.GetString(1);
            }
            reader.Close();
            return str;
        }

        public DataTable GetTablesInfo(string DbName)
        {
            string sQLString = "SHOW TABLE STATUS";
            DataTable table = this.Query(DbName, sQLString).Tables[0];
            table.Columns[0].ColumnName = "name";
            DataRow[] rowArray = table.Select("Comment<>'VIEW' and Engine is not null");
            DataTable table2 = table.Clone();
            foreach (DataRow row in rowArray)
            {
                DataRow row2 = table2.NewRow();
                row2["name"] = row["name"];
                table2.Rows.Add(row2);
            }
            return table2;
        }

        public DataTable GetTablesSP(string DbName)
        {
            MySqlParameter[] parameterArray = new MySqlParameter[] { new MySqlParameter("@table_name", MySqlDbType.VarChar, 0x180), new MySqlParameter("@table_owner", MySqlDbType.VarChar, 0x180), new MySqlParameter("@table_qualifier", MySqlDbType.VarChar, 0x180), new MySqlParameter("@table_type", MySqlDbType.VarChar, 100) };
            parameterArray[0].Value = null;
            parameterArray[1].Value = null;
            parameterArray[2].Value = null;
            parameterArray[3].Value = "'TABLE'";
            IDataParameter[] parameters = parameterArray;
            DataSet set = this.RunProcedure(DbName, "sp_tables", parameters, "ds");
            if (set.Tables.Count > 0)
            {
                DataTable table = set.Tables[0];
                table.Columns["TABLE_QUALIFIER"].ColumnName = "db";
                table.Columns["TABLE_OWNER"].ColumnName = "cuser";
                table.Columns["TABLE_NAME"].ColumnName = "name";
                table.Columns["TABLE_TYPE"].ColumnName = "type";
                table.Columns["REMARKS"].ColumnName = "remarks";
                return table;
            }
            return null;
        }

        public DataTable GetTabViews(string DbName)
        {
            string sQLString = "SHOW TABLES";
            DataTable table = this.Query(DbName, sQLString).Tables[0];
            table.Columns[0].ColumnName = "name";
            return table;
        }

        public DataTable GetTabViewsInfo(string DbName)
        {
            string sQLString = "SHOW TABLE STATUS";
            DataTable table = this.Query(DbName, sQLString).Tables[0];
            table.Columns[0].ColumnName = "name";
            return table;
        }

        public DataTable GetTabViewsSP(string DbName) =>
            this.GetTabViews(DbName);

        public string GetVersion()
        {
            try
            {
                string sQLString = "execute master..sp_msgetversion ";
                return this.Query("master", sQLString).Tables[0].Rows[0][0].ToString();
            }
            catch
            {
                return "";
            }
        }

        public DataTable GetVIEWs(string DbName)
        {
            string sQLString = "SHOW TABLE STATUS";
            DataTable table = this.Query(DbName, sQLString).Tables[0];
            table.Columns[0].ColumnName = "name";
            DataRow[] rowArray = table.Select("Comment='VIEW' and Engine is  null");
            DataTable table2 = table.Clone();
            foreach (DataRow row in rowArray)
            {
                DataRow row2 = table2.NewRow();
                row2["name"] = row["name"];
                table2.Rows.Add(row2);
            }
            return table2;
        }

        public DataTable GetVIEWsInfo(string DbName)
        {
            string sQLString = "SHOW TABLE STATUS";
            DataTable table = this.Query(DbName, sQLString).Tables[0];
            table.Columns[0].ColumnName = "name";
            DataRow[] rowArray = table.Select("Comment='VIEW' and Engine is null");
            DataTable table2 = table.Clone();
            foreach (DataRow row in rowArray)
            {
                DataRow row2 = table2.NewRow();
                row2["name"] = row["name"];
                table2.Rows.Add(row2);
            }
            return table2;
        }

        public DataTable GetVIEWsSP(string DbName)
        {
            MySqlParameter[] parameterArray = new MySqlParameter[] { new MySqlParameter("@table_name", MySqlDbType.VarChar, 0x180), new MySqlParameter("@table_owner", MySqlDbType.VarChar, 0x180), new MySqlParameter("@table_qualifier", MySqlDbType.VarChar, 0x180), new MySqlParameter("@table_type", MySqlDbType.VarChar, 100) };
            parameterArray[0].Value = null;
            parameterArray[1].Value = null;
            parameterArray[2].Value = null;
            parameterArray[3].Value = "'VIEW'";
            IDataParameter[] parameters = parameterArray;
            DataSet set = this.RunProcedure(DbName, "sp_tables", parameters, "ds");
            if (set.Tables.Count > 0)
            {
                DataTable table = set.Tables[0];
                table.Columns["TABLE_QUALIFIER"].ColumnName = "db";
                table.Columns["TABLE_OWNER"].ColumnName = "cuser";
                table.Columns["TABLE_NAME"].ColumnName = "name";
                table.Columns["TABLE_TYPE"].ColumnName = "type";
                table.Columns["REMARKS"].ColumnName = "remarks";
                return table;
            }
            return null;
        }

        private bool IsDboSp() =>
            this.isdbosp;

        private MySqlCommand OpenDB(string DbName)
        {
            try
            {
                if (this.connect.ConnectionString == "")
                {
                    this.connect.ConnectionString = this._dbconnectStr;
                }
                if (this.connect.ConnectionString != this._dbconnectStr)
                {
                    this.connect.Close();
                    this.connect.ConnectionString = this._dbconnectStr;
                }
                MySqlCommand command = new MySqlCommand
                {
                    Connection = this.connect
                };
                if (this.connect.State == ConnectionState.Closed)
                {
                    this.connect.Open();
                }
                command.CommandText = ("use " + DbName) ?? "";
                command.ExecuteNonQuery();
                return command;
            }
            catch (Exception exception)
            {
                string message = exception.Message;
                return null;
            }
        }

        public DataSet Query(string DbName, string SQLString)
        {
            DataSet dataSet = new DataSet();
            try
            {
                this.OpenDB(DbName);
                new MySqlDataAdapter(SQLString, this.connect).Fill(dataSet, "ds");
            }
            catch (MySqlException exception)
            {
                throw new Exception(exception.Message);
            }
            return dataSet;
        }

        public bool RenameTable(string DbName, string OldName, string NewName)
        {
            try
            {
                MySqlCommand command = this.OpenDB(DbName);
                command.CommandText = ("RENAME TABLE " + OldName + " TO " + NewName) ?? "";
                command.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public DataSet RunProcedure(string DbName, string storedProcName, IDataParameter[] parameters, string tableName)
        {
            this.OpenDB(DbName);
            DataSet dataSet = new DataSet();
            new MySqlDataAdapter { SelectCommand = this.BuildQueryCommand(this.connect, storedProcName, parameters) }.Fill(dataSet, tableName);
            return dataSet;
        }

        private void TypeNameProcess(string strName, out string TypeName, out string Length, out string Preci, out string Scale)
        {
            int num3;
            int num4;
            TypeName = strName;
            int index = strName.IndexOf("(");
            Length = "";
            Preci = "";
            Scale = "";
            if (index > 0)
            {
                TypeName = strName.Substring(0, index);
                string s = TypeName.Trim().ToUpper();
                if (s == "TIMESTAMP" || s == "BIGINT" || s == "VARCHAR" || s == "SMALLINT" || s == "INTEGER" || s == "CHAR" || s == "MEDIUMINT" || s == "INT")
                {
                    num3 = strName.IndexOf(")");
                    Length = strName.Substring(index + 1, (num3 - index) - 1);
                }
                else if (s == "DEC" || s == "DECIMAL" || s == "NUMERIC")
                {
                    num4 = strName.IndexOf(")");
                    string str2 = strName.Substring(index + 1, (num4 - index) - 1);
                    int length = str2.IndexOf(",");
                    Length = str2.Substring(0, length);
                    Scale = str2.Substring(length + 1);
                }
            }
            else
            {
                TypeName = strName;
            }
        }

        public string DbConnectStr
        {
            get =>
                this._dbconnectStr;
            set
            {
                this._dbconnectStr = value;
            }
        }

        public string DbType =>
            "MySQL";
    }
}
